﻿using System;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Web;
using Framework;

namespace Enterprise
{
    public class ProductService : BaseService
    {
        public int Edit(int id, string name, string nameEn, int productCategoryId, string description, string descriptionEn, string model, bool isRecommend, int coverId, string keyword = "", string keywordEn = "", string finish = "", string finishEn = "", string material = "", string materialEn = "", string doorThickness = "", string backset = "", string availableFor = "", string availableForEn = "", string applicableStandard = "", string size = "", string modelEn = "", string summary = "", string summaryEn = "")
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "产品名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "产品名称(En)");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update tbproduct set ");
            strSqlOne.Append("CoName=@CoName");
            strSqlOne.Append(",");
            strSqlOne.Append("CoNameEn=@CoNameEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoProductCategoryId=@CoProductCategoryId");
            strSqlOne.Append(",");
            strSqlOne.Append("CoCategoryPath=@CoCategoryPath");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDescription=@CoDescription");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDescriptionEn=@CoDescriptionEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoModel=@CoModel");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIsRecommend=@CoIsRecommend");
            strSqlOne.Append(",");
            strSqlOne.Append("CoNormalImage=@CoNormalImage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSmallImage=@CoSmallImage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoBigImage=@CoBigImage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoCoverImageId=@CoCoverImageId");
            strSqlOne.Append(",");
            strSqlOne.Append("CoKeyword=@CoKeyword");
            strSqlOne.Append(",");
            strSqlOne.Append("CoFinish=@CoFinish");
            strSqlOne.Append(",");
            strSqlOne.Append("CoMaterial=@CoMaterial");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDoorThickness=@CoDoorThickness");
            strSqlOne.Append(",");
            strSqlOne.Append("CoBackset=@CoBackset");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAvailableFor=@CoAvailableFor");
            strSqlOne.Append(",");
            strSqlOne.Append("CoApplicableStndard=@CoApplicableStndard");
            strSqlOne.Append(",");
            strSqlOne.Append("CoKeywordEn=@CoKeywordEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoFinishEn=@CoFinishEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoMaterialEn=@CoMaterialEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAvailableForEn=@CoAvailableForEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSize=@CoSize");
            strSqlOne.Append(",");
            strSqlOne.Append("CoModelEn=@CoModelEn");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSummary=@CoSummary");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSummaryEn=@CoSummaryEn");
            strSqlOne.Append(" where coproductId=@id;");
            strSqlOne.Append("update TbProductImage set CoIsCover=0 where CoProductId=@id and CoIsCover=1;");
            strSqlOne.Append("update TbProductImage set CoIsCover=1 where CoProductImageId=@CoCoverImageId;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoDescription", DbType.String),
                    new SQLiteParameter("@CoProductCategoryId", DbType.String),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoModel", DbType.String, 255),
                    new SQLiteParameter("@CoIsRecommend", DbType.Boolean),
                    new SQLiteParameter("@CoCoverImageId", DbType.String, 255),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255),
                    new SQLiteParameter("@id", DbType.Int32),
                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                    new SQLiteParameter("@CoDescriptionEn", DbType.String),
                    new SQLiteParameter("@CoKeyword", DbType.String, 255),
                    new SQLiteParameter("@CoFinish", DbType.String, 255),
                    new SQLiteParameter("@CoMaterial", DbType.String, 255),
                    new SQLiteParameter("@CoDoorThickness", DbType.String, 255),
                    new SQLiteParameter("@CoBackset", DbType.String, 255),
                    new SQLiteParameter("@CoAvailableFor", DbType.String, 255),
                    new SQLiteParameter("@CoApplicableStndard", DbType.String, 255),
                    new SQLiteParameter("@CoKeywordEn", DbType.String, 255),
                    new SQLiteParameter("@CoFinishEn", DbType.String, 255),
                    new SQLiteParameter("@CoMaterialEn", DbType.String, 255),
                    new SQLiteParameter("@CoAvailableForEn", DbType.String, 255),
                    new SQLiteParameter("@CoSize", DbType.String, 255),
                    new SQLiteParameter("@CoModelEn", DbType.String, 255),
                    new SQLiteParameter("@CoSummary",  DbType.String, 255),
                    new SQLiteParameter("@CoSummaryEn",  DbType.String, 255)
                };
            parametersOne[0].Value = name;
            parametersOne[1].Value = description;
            var parentParamTwo = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parentParamTwo[0].Value = productCategoryId;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from tbproductcategory where CoproductCategoryId=@id", parentParamTwo))
            {

                if (sdr.Read())
                {
                    parametersOne[2].Value = productCategoryId;
                    parametersOne[3].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    throw new Exception("分类不存在");
                }
            }
            parametersOne[4].Value = model;
            parametersOne[5].Value = isRecommend;

            var parentParamThree = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parentParamThree[0].Value = coverId;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbProductImage where CoProductImageId=@id", parentParamThree))
            {

                if (sdr.Read())
                {
                    parametersOne[6].Value = coverId;
                    parametersOne[7].Value = sdr["CoNormalImage"].SafeToString();
                    parametersOne[8].Value = sdr["CoSmallImage"].SafeToString();
                    parametersOne[9].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parametersOne[6].Value = DBNull.Value;
                    parametersOne[7].Value = DBNull.Value;
                    parametersOne[8].Value = DBNull.Value;
                    parametersOne[9].Value = DBNull.Value;
                }
            }
            parametersOne[10].Value = id;
            parametersOne[11].Value = nameEn;
            parametersOne[12].Value = descriptionEn;

            parametersOne[13].Value = keyword;
            parametersOne[14].Value = finish;
            parametersOne[15].Value = material;
            parametersOne[16].Value = doorThickness;
            parametersOne[17].Value = backset;
            parametersOne[18].Value = availableFor;
            parametersOne[19].Value = applicableStandard;

            parametersOne[20].Value = keywordEn;
            parametersOne[21].Value = finishEn;
            parametersOne[22].Value = materialEn;
            parametersOne[23].Value = availableForEn;
            parametersOne[24].Value = size;
            parametersOne[25].Value = modelEn;
            parametersOne[26].Value = summary;
            parametersOne[27].Value = summaryEn;
            var rows = DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt32();
            return rows;

        }
        public int Add(string name, string nameEn, int productCategoryId, string description, string descriptionEn, string model, bool isRecommend, int[] productImageIds, int coverId, string keyword = "", string keywordEn = "", string finish = "", string finishEn = "", string material = "", string materialEn = "", string doorThickness = "", string backset = "", string availableFor = "", string availableForEn = "", string applicableStandard = "", string size = "", string modelEn = "", string summary = "", string summaryEn = "")
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "产品名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "产品名称(En)");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into tbproduct(");
            strSqlOne.Append("CoName,CoNameEn,CoProductCategoryId,CoDescription,CoDescriptionEn,CoCategoryPath,CoModel,CoIsRecommend,CoNormalImage,CoSmallImage,CoBigImage,CoCoverImageId,CoKeyword,CoFinish,CoMaterial,CoDoorThickness,CoBackset,CoAvailableFor,CoApplicableStndard,CoKeywordEn,CoFinishEn,CoMaterialEn,CoAvailableForEn,CoSize,CoModelEn,CoSummary,CoSummaryEn");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoName,@CoNameEn,@CoProductCategoryId,@CoDescription,@CoDescriptionEn,@CoCategoryPath,@CoModel,@CoIsRecommend,@CoNormalImage,@CoSmallImage,@CoBigImage,@CoCoverImageId,@CoKeyword,@CoFinish,@CoMaterial,@CoDoorThickness,@CoBackset,@CoAvailableFor,@CoApplicableStndard,@CoKeywordEn,@CoFinishEn,@CoMaterialEn,@CoAvailableForEn,@CoSize,@CoModelEn,@CoSummary,@CoSummaryEn");
            strSqlOne.Append(");");
            strSqlOne.AppendFormat("update TbProductImage set CoProductId=(select last_insert_rowid() from tbproduct) where CoProductImageId in ({0}) and  exists(select * from tbproduct where coproductid=(select last_insert_rowid() from tbproduct));", string.Join(",", productImageIds.Select(o => o.ToString(CultureInfo.InvariantCulture)).ToArray()));
            strSqlOne.Append("update TbProductImage set CoIsCover=1 where CoProductImageId=@CoCoverImageId;");
            strSqlOne.Append("select last_insert_rowid() from tbproduct;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoDescription", DbType.String),
                    new SQLiteParameter("@CoProductCategoryId", DbType.String),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoModel", DbType.String, 255),
                    new SQLiteParameter("@CoIsRecommend", DbType.Boolean),
                    new SQLiteParameter("@CoCoverImageId", DbType.String, 255),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255),
                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                    new SQLiteParameter("@CoDescriptionEn", DbType.String),
                    new SQLiteParameter("@CoKeyword", DbType.String, 255),
                    new SQLiteParameter("@CoFinish", DbType.String, 255),
                    new SQLiteParameter("@CoMaterial", DbType.String, 255),
                    new SQLiteParameter("@CoDoorThickness", DbType.String, 255),
                    new SQLiteParameter("@CoBackset", DbType.String, 255),
                    new SQLiteParameter("@CoAvailableFor", DbType.String, 255),
                    new SQLiteParameter("@CoApplicableStndard", DbType.String, 255),
                    new SQLiteParameter("@CoKeywordEn", DbType.String, 255),
                    new SQLiteParameter("@CoFinishEn", DbType.String, 255),
                    new SQLiteParameter("@CoMaterialEn", DbType.String, 255),
                    new SQLiteParameter("@CoAvailableForEn", DbType.String, 255),
                    new SQLiteParameter("@CoSize", DbType.String, 255),
                    new SQLiteParameter("@CoModelEn", DbType.String, 255),
                    new SQLiteParameter("@CoSummary",  DbType.String, 255),
                    new SQLiteParameter("@CoSummaryEn",  DbType.String, 255)

                };
            parametersOne[0].Value = name;
            parametersOne[1].Value = description;
            var parentParamTwo = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parentParamTwo[0].Value = productCategoryId;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from tbproductcategory where CoproductCategoryId=@id", parentParamTwo))
            {

                if (sdr.Read())
                {
                    parametersOne[2].Value = productCategoryId;
                    parametersOne[3].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    throw new Exception("分类不存在");
                }
            }
            parametersOne[4].Value = model;
            parametersOne[5].Value = isRecommend;

            var parentParamThree = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parentParamThree[0].Value = coverId;
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from TbProductImage where CoProductImageId=@id", parentParamThree))
            {

                if (sdr.Read())
                {
                    parametersOne[6].Value = coverId;
                    parametersOne[7].Value = sdr["CoNormalImage"].SafeToString();
                    parametersOne[8].Value = sdr["CoSmallImage"].SafeToString();
                    parametersOne[9].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parametersOne[6].Value = DBNull.Value;
                    parametersOne[7].Value = DBNull.Value;
                    parametersOne[8].Value = DBNull.Value;
                    parametersOne[9].Value = DBNull.Value;
                }
            }
            parametersOne[10].Value = nameEn;
            parametersOne[11].Value = descriptionEn;
            parametersOne[12].Value = keyword;
            parametersOne[13].Value = finish;
            parametersOne[14].Value = material;
            parametersOne[15].Value = doorThickness;
            parametersOne[16].Value = backset;
            parametersOne[17].Value = availableFor;
            parametersOne[18].Value = applicableStandard;
            parametersOne[19].Value = keywordEn;
            parametersOne[20].Value = finishEn;
            parametersOne[21].Value = materialEn;
            parametersOne[22].Value = availableForEn;
            parametersOne[23].Value = size;
            parametersOne[24].Value = modelEn;
            parametersOne[25].Value = summary;
            parametersOne[26].Value = summaryEn;
            var obj = DbHelperSqLite.GetSingle(strSqlOne.ToString(), parametersOne).SafeToInt32();
            return obj;
        }
        public void Delete(int id)
        {
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("delete  from TbProduct  where CoProductId=@id;");
            strSqlOne.Append("delete  from tbProductimage  where CoProductId=@id;");
            var parameterOne = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parameterOne[0].Value = id;
            var path = HttpContext.Current.Server.MapPath("~/Upload/Product/");
            var sb = new StringBuilder();
            sb.Append("select * from tbProductimage where CoProductId=@id");
            var parameterTwo = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parameterTwo[0].Value = id;
            var ds = DbHelperSqLite.Query(sb.ToString(), parameterTwo);
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parameterOne);
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string name, string model, int productCategoryId, string isRecommend, int pageValue, int rowsValue)
        {
            var parOne = new[]
                             {
                                 new SQLiteParameter("@CoName", DbType.String, 255),
                                 new SQLiteParameter("@CoModel", DbType.String, 255),
                                 new SQLiteParameter("@CoProductCategoryId", DbType.Int32),
                                 new SQLiteParameter("@CoIsRecommend", DbType.Boolean)
                             };
            parOne[0].Value = name.SqliteLike();
            parOne[1].Value = model.SqliteLike();
            parOne[2].Value = productCategoryId;
            parOne[3].Value = isRecommend.SafeToBool();
            var strWhere = new StringBuilder();
            strWhere.Append(" where (A.CoName like '%'||@CoName||'%' escape '\\' or A.CoNameEn like '%'||@CoName||'%' escape '\\' ) and  (A.CoModel like '%'||@CoModel||'%' escape '\\' Or  A.CoModelEn like '%'||@CoModel||'%' escape '\\' )");
            if (productCategoryId != 0)
            {
                strWhere.Append(" and A.CoProductCategoryId=@CoProductCategoryId");
            }
            if (!isRecommend.IsNullOrEmpty())
            {
                strWhere.Append(" and A.CoIsRecommend=@CoIsRecommend");
            }
            parOne[0].Value = name;
            var sqlOne = new StringBuilder();
            sqlOne.Append("select count(*) from TbProduct as A ");
            sqlOne.Append(strWhere);
            int total = DbHelperSqLite.GetSingle(sqlOne.ToString(), parOne).SafeToInt();

            var parTwo = new[]
                           {
                           
                               new SQLiteParameter("@startIndex", DbType.Int32),
                               new SQLiteParameter("@endIndex", DbType.Int32),   
                               new SQLiteParameter("@CoName", DbType.String, 255),
                               new SQLiteParameter("@CoModel", DbType.String, 255),
                               new SQLiteParameter("@CoProductCategoryId", DbType.Int32),
                               new SQLiteParameter("@CoIsRecommend", DbType.Boolean)
                           };
            parTwo[0].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parTwo[1].Value = rowsValue;
            parTwo[2].Value = name;
            parTwo[3].Value = model;
            parTwo[4].Value = productCategoryId;
            parTwo[5].Value = isRecommend.SafeToBool();
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select A.CoProductId,A.CoName,A.CoSummary,A.CoSummaryEn,A.CoNameEn,A.CoSort,A.CoProductCategoryId,A.CoIsRecommend,A.CoNormalImage,A.CoSmallImage, B.CoName as CoCategoryName from TbProduct as A left join TbProductCategory as B on  A.CoProductCategoryId=B.CoProductCategoryId ");
            sqlTwo.Append(strWhere);
            sqlTwo.Append(" order by A.CoSort Desc, A.coproductid ");
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parTwo);
            var table = new Table
                {
                    Rows = ds.Tables["ds"].Rows,
                    Total = total
                };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            parameters[0].Value = id;
            var rows = DbHelperSqLite.Query("select *  from tbproduct  where CoProductId=@id", parameters).Tables["ds"].Rows;
            return rows[0];
        }

    }
}
